package bysj.dao;


import bysj.domain.Degree;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class DegreeDao {
    private static DegreeDao degreeDao = null;


    private DegreeDao() {
    }

    public static DegreeDao getInstance() {
        if(degreeDao==null){
            degreeDao = new DegreeDao();
        }
        return degreeDao;
    }

    public Collection<Degree> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException {
        //创建集合类对象，用来保存所有记录代表的Department对象
        Collection<Degree> degrees = new HashSet<>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM degree");
        int totalNum = CommonDao.getInstance().count(connection,"degree");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
            //System.out.println(sql_select);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            //获得result的参数值
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            //为department赋值
            Degree degree = new Degree(id, description, no, remarks);
            //将department添加到departments集合里
            degrees.add(degree);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement);
        //返回departments集合
        return degrees;
    }

    public Degree find(Integer id,Connection connection) throws SQLException {
        Degree degree = null;
        //创建sql语句
        String degree_sql = "SELECT * FROM degree WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt = connection.prepareStatement(degree_sql);
        //为预编译对象赋值
        pstmt.setInt(1, id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            //获得result的参数值
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            //为department赋值
            degree = new Degree(id, description, no, remarks);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return degree;
    }

    public boolean update(Degree degree,Connection connection) throws SQLException {
        //准备预编译语句
        String degree_sql = "update degree set description=?,no=?,remarks=? where id=" + degree.getId();
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement(degree_sql);
        //为预编译对象赋值
        preparedStatement.setString(1, degree.getDescription());
        preparedStatement.setString(2, degree.getNo());
        preparedStatement.setString(3, degree.getRemarks());
        //执行预编译对象的executeUpdate方法，获得修改的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }
    public boolean add(Degree degree,Connection connection) throws SQLException {
        //创建预编译对象
        PreparedStatement preparedStatement = connection.prepareStatement("Insert into degree(description,no,remarks) Values " + "(?,?,?)");
        //为预编译对象赋值
        preparedStatement.setString(1, degree.getDescription());
        preparedStatement.setString(2, degree.getNo());
        preparedStatement.setString(3, degree.getRemarks());
        //执行预编译对象的executeUpdate方法，获得增加的记录行数
        int rowAdd = preparedStatement.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(preparedStatement);
        return rowAdd > 0;
    }

    public boolean delete(Integer id,Connection connection) throws SQLException {
        //创建sql语句
        String degree_sql = "delete from degree where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(degree_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(pstmt);
        return affectedRowNum > 0;
    }

}

